package com.example.myapplication.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "school.db";
    public static final int DB_VERSION = 3; // 将版本号升级到3

    public static final String TABLE_COURSE = "course";
    public static final String TABLE_TEACHER = "teacher";
    public static final String TABLE_REVIEW = "review";

    public DBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建表
        db.execSQL("CREATE TABLE IF NOT EXISTS teacher (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, info TEXT)");
        db.execSQL("CREATE TABLE IF NOT EXISTS course (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, teacher_id INTEGER, info TEXT, FOREIGN KEY(teacher_id) REFERENCES teacher(id))");
        db.execSQL("CREATE TABLE IF NOT EXISTS review (id INTEGER PRIMARY KEY AUTOINCREMENT, course_id INTEGER, reviewer TEXT NOT NULL, rating INTEGER, comment TEXT, time TEXT, like_count INTEGER DEFAULT 0, FOREIGN KEY(course_id) REFERENCES course(id))");

        // 创建索引以提高查询性能
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_teacher_name ON teacher(name)");
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_course_name ON course(name)");
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_course_teacher ON course(teacher_id)");
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_review_course ON review(course_id)");

        // 插入示例数据：5个老师
        db.execSQL("INSERT INTO teacher (name, info) VALUES " +
                "('张三', '资深数学教授，教学经验丰富')," +
                "('李四', '计算机科学副教授，专注人工智能研究')," +
                "('王五', '物理系教授，量子力学专家')," +
                "('赵六', '英语系讲师，口语教学见长')," +
                "('钱七', '化学系教授，有机化学研究员')");

        // 为每个老师添加2-3门课程
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '高等数学', id FROM teacher WHERE name='张三'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '线性代数', id FROM teacher WHERE name='张三'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '人工智能导论', id FROM teacher WHERE name='李四'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '机器学习', id FROM teacher WHERE name='李四'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '量子力学', id FROM teacher WHERE name='王五'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '大学物理', id FROM teacher WHERE name='王五'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '大学英语', id FROM teacher WHERE name='赵六'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '英语口语', id FROM teacher WHERE name='赵六'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '有机化学', id FROM teacher WHERE name='钱七'");
        db.execSQL("INSERT INTO course (name, teacher_id) SELECT '普通化学', id FROM teacher WHERE name='钱七'");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 在升级时删除旧表并重新创建
        db.execSQL("DROP TABLE IF EXISTS review");
        db.execSQL("DROP TABLE IF EXISTS course");
        db.execSQL("DROP TABLE IF EXISTS teacher");
        onCreate(db);
    }
}
